<?php 

CREATE TABLE `father` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` char(20) NOT NULL DEFAULT '1' COMMENT '父亲名',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='父亲表';


CREATE TABLE `class` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `classname` char(20) NOT NULL DEFAULT '1' COMMENT '父亲名',
  `tid` int(5) NOT NULL DEFAULT '1' COMMENT '老师id',

  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='父亲表';




CREATE TABLE `teacher` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `classname` char(20) NOT NULL DEFAULT '1' COMMENT '老师名',
  `sex` enum ('男', '女', '保密') NOT NULL DEFAULT '保密' COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='老师表';

CREATE TABLE `mid` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `cid` int(5) NOT NULL DEFAULT '0' COMMENT '班级id',
  `tid` int(5) NOT NULL DEFAULT '0' COMMENT '老师id',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='中间表';





CREATE TABLE `class` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `classname` char(20) DEFAULT NULL COMMENT '班级名',
  `detail` char(20) DEFAULT NULL COMMENT '详情',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='班级表';



CREATE TABLE `teacher` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `tname` char(20) DEFAULT NULL COMMENT '老师名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='老师表';


CREATE TABLE `class_teacher` (
  `cid` int(10) COMMENT '班级id',
  `tid` int(10)  COMMENT '老师id'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='班级与老师中间表';







//

mysql> select stu.id,stu.name,stu_ext.id,stu_ext.sid,stu_ext.father from stu,stu_ext;//25条结果

mysql> select stu.id,stu.name,stu_ext.id,stu_ext.sid,stu_ext.father from stu,stu_ext where stu.id=stu_ext.sid;//四条符合条件

mysql> select t1.name,t2.father from stu t1 inner join stu_ext t2 on t1.id=t2.sid;//一对一，每个学生对应的扩展信息

mysql>select t1.name,t2.classname from stu t1 inner join class t2 on t1.cid=t2.id;//一对多，获取每个学生对应的班级

mysql>select * from class t1 inner join class_teacher t2 on t1.id=t2.cid inner join teacher t3 on t2.tid=t3.id//多对多，三表链接


mysql>select * from class t1 inner join class_teacher t2 on t1.id=t2.cid inner join teacher t3 on t2.tid=t3.id where t1.classname='php';



mysql>select t1.name,t2.name from stu t1 inner jion stu t2 on t1.cid=t2.cid where t1.name='小明';


select count(stu.sex) t  from stu inner join class on stu.cid=class.id where stu.sex='女' group by class.name having t =(select count(stu.sex) t  from stu inner join class on stu.cid=class.id where stu.sex='女' group by class.name order by t limit 1);    		//女生最多的班级

select count(stu.sex) t  from stu inner join class on stu.cid=class.id where stu.sex='女' group by class.name having t =1;








 ?>